#coding: utf-8
#!/usr/bin/python

import sys
sys.path.append("..")

# 设置代码中用到的路径，可以放在这里主要是用于在不同操作系统中应用时易于修改
PATH = r"C:\Users\Administrator\Desktop\Task_Sql\Data\\" # 注意末尾，要用两个\表示一个
from matplotlib.font_manager import FontProperties
FONT = FontProperties(fname=r"c:\windows\fonts\simsun.ttc", size=14)

# 设置cookies
from GetCookie import CookiesMain
COOKIES = CookiesMain()

MAX_THREADING = 4

BaseUrl = "http://www.appchina.com/soft"
ProxyUrl = "http://www.kuaidaili.com/proxylist/"
TimeSleep = 1

# 下载模块中的默认数据
DEFAULT_AGENT = 'wswp'
DEFAULT_DELAY = 30 # 设置系统的默认超时时间
DEFAULT_RETRIES = 1
DEFAULT_TIMEOUT = 5

# 邮件默认要发送的内容
CONTENT = """
爬虫爬取完毕！
"""

# 设置当前代理ip不能用的时候默认访问的次数
MAX_NUM = 3

# 创建触发器（http://www.cnblogs.com/nicholas_f/archive/2009/09/22/1572050.html）
TRIGGER = """
    CREATE TRIGGER tigger_info
    AFTER INSERT ON soft_info
    FOR EACH ROW
    begin
    insert into soft_classification(url, name, classification) values(new.url, new.name, new.classification);
    end;
"""

# 创建第一个存储过程
PROCEDURE1 = """
    create procedure p1 (u varchar(300), n varchar(300))
    begin
    insert into pageurl(url, name) values(u, n);
    end;
"""
# 创建第二个存储过程
PROCEDURE2 = """
    create procedure p2 (u varchar(300), n varchar(300), s varchar(100), up varchar(100), ed varchar(100), fo varchar(100), cla varchar(100), req varchar(100), jur text, comm varchar(100), ex varchar(100))
    begin
    insert into all_info(url, name, size, _update, edition, _format, classification, requirement, jurisdiction, comment, exp) values(u, n, s, up, ed, fo, cla, req, jur, comm, ex);
    end;
"""

# 分类信息
Create_Classification = """
create table if not exists soft_classification (
    soft_id smallint UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    url varchar(300) not null unique key,
    name varchar(300) not null,
    classification varchar(100) not null
);
"""

# 软件信息
Create_Soft_Info = """
create table if not exists soft_info (
    soft_id smallint UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    url varchar(300) not null unique key,
    name varchar(300) not null,
    size varchar(100) null,
    _update varchar(100) null,
    edition varchar(100) null,
    _format varchar(100) null,
    classification varchar(100) not null,
    requirement varchar(100) null,
    jurisdiction text null
);
"""
# 查找soft_info数据库中的字段
Select_Sql_Soft_Info = "select soft_id, url, name, size, _update, edition, _format, classification, requirement, jurisdiction from soft_info;"

# 软件概况
Create_Soft_Survey = """
    create table if not exists soft_survey(
    soft_id smallint UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    url varchar(300) not null unique key,
    name varchar(300) not null,
    comment varchar(100) not null,
    exp varchar(100) not null
    );
"""
# 查看soft_survey数据库中的字段
Select_Sql_Soft_Survey = "select soft_id, url, name, comment, exp from soft_survey;"

# 每一个应用链接的表
CreatePageUrl = """
    create table if not exists pageurl (
    id int unsigned not null primary key auto_increment,
    url varchar(300) not null unique key,
    name varchar(300) not null
);
"""
# 查询语句
Select_Sql_Page_Url = "select id, url, name from pageurl;"
Select_Sql_Page_Url2 = "select url, name from pageurl;"

# 创建一个每个应用的所有信息的表，因为总程序最后需要进行测试，为了防止数据丢失，先把所有信息放入这个表中
Create_Soft_AllInfo = """
    create table if not exists all_info(
    soft_id smallint UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    url varchar(300) not null unique key,
    name varchar(300) not null,
    size varchar(100) null,
    _update varchar(100) null,
    edition varchar(100) null,
    _format varchar(100) null,
    classification varchar(100) not null,
    comment varchar(100) not null,
    exp varchar(100) not null,
    requirement varchar(100) null,
    jurisdiction text null
);
"""
# 向all_info表中插入数据
Insert_Sql_AllInfo = "insert into all_info(url, name, size, _update, edition, _format, classification, requirement, jurisdiction, comment, exp) values('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s');"
# 读取all_info表中的url列
Select_Sql_AllInfo = "select url from all_info;"
Select_Sql_AllInfo2 = "select comment, classification from all_info;"

# ip的表每次都要把原来的字段删除，因为已经没有用了，所以在创建此表之前都执行了删除操作
CreateKuaiIP = """
    create table kuaiIP(
    id int unsigned not null primary key auto_increment,
    ip varchar(100) not null,
    port varchar(100) not null,
    type varchar(100) not null,
    url varchar(100) not null
    );
"""

import pymysql
conn =  pymysql.connect(
    host = "127.0.0.1",
    port = 3306,
    user = "root",
    password = "root",
    charset = "utf8",
)

USER_AGENTS = [
    "Mozilla/5.0 (Linux; U; Android 2.3.6; en-us; Nexus S Build/GRK39F) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1",
    "Avant Browser/1.2.789rel1 (http://www.avantbrowser.com)",
    "Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/532.5 (KHTML, like Gecko) Chrome/4.0.249.0 Safari/532.5",
    "Mozilla/5.0 (Windows; U; Windows NT 5.2; en-US) AppleWebKit/532.9 (KHTML, like Gecko) Chrome/5.0.310.0 Safari/532.9",
    "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/534.7 (KHTML, like Gecko) Chrome/7.0.514.0 Safari/534.7",
    "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/534.14 (KHTML, like Gecko) Chrome/9.0.601.0 Safari/534.14",
    "Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/534.14 (KHTML, like Gecko) Chrome/10.0.601.0 Safari/534.14",
    "Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/534.20 (KHTML, like Gecko) Chrome/11.0.672.2 Safari/534.20",
    "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/534.27 (KHTML, like Gecko) Chrome/12.0.712.0 Safari/534.27",
    "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.1 (KHTML, like Gecko) Chrome/13.0.782.24 Safari/535.1",
    "Mozilla/5.0 (Windows NT 6.0) AppleWebKit/535.2 (KHTML, like Gecko) Chrome/15.0.874.120 Safari/535.2",
    "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.7 (KHTML, like Gecko) Chrome/16.0.912.36 Safari/535.7",
    "Mozilla/5.0 (Windows; U; Windows NT 6.0 x64; en-US; rv:1.9pre) Gecko/2008072421 Minefield/3.0.2pre",
    "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.10) Gecko/2009042316 Firefox/3.0.10",
    "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-GB; rv:1.9.0.11) Gecko/2009060215 Firefox/3.0.11 (.NET CLR 3.5.30729)",
    "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.1.6) Gecko/20091201 Firefox/3.5.6 GTB5",
    "Mozilla/5.0 (Windows; U; Windows NT 5.1; tr; rv:1.9.2.8) Gecko/20100722 Firefox/3.6.8 ( .NET CLR 3.5.30729; .NET4.0E)",
    "Mozilla/5.0 (Windows NT 6.1; rv:2.0.1) Gecko/20100101 Firefox/4.0.1",
    "Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:2.0.1) Gecko/20100101 Firefox/4.0.1",
    "Mozilla/5.0 (Windows NT 5.1; rv:5.0) Gecko/20100101 Firefox/5.0",
    "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:6.0a2) Gecko/20110622 Firefox/6.0a2",
    "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:7.0.1) Gecko/20100101 Firefox/7.0.1",
    "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:2.0b4pre) Gecko/20100815 Minefield/4.0b4pre",
    "Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0 )",
    "Mozilla/4.0 (compatible; MSIE 5.5; Windows 98; Win 9x 4.90)",
    "Mozilla/5.0 (Windows; U; Windows XP) Gecko MultiZilla/1.6.1.0a",
    "Mozilla/2.02E (Win95; U)",
    "Mozilla/3.01Gold (Win95; I)",
    "Mozilla/4.8 [en] (Windows NT 5.1; U)",
    "Mozilla/5.0 (Windows; U; Win98; en-US; rv:1.4) Gecko Netscape/7.1 (ax)",
    "HTC_Dream Mozilla/5.0 (Linux; U; Android 1.5; en-ca; Build/CUPCAKE) AppleWebKit/528.5  (KHTML, like Gecko) Version/3.1.2 Mobile Safari/525.20.1",
    "Mozilla/5.0 (hp-tablet; Linux; hpwOS/3.0.2; U; de-DE) AppleWebKit/534.6 (KHTML, like Gecko) wOSBrowser/234.40.1 Safari/534.6 TouchPad/1.0",
    "Mozilla/5.0 (Linux; U; Android 1.5; en-us; sdk Build/CUPCAKE) AppleWebkit/528.5  (KHTML, like Gecko) Version/3.1.2 Mobile Safari/525.20.1",
    "Mozilla/5.0 (Linux; U; Android 2.1; en-us; Nexus One Build/ERD62) AppleWebKit/530.17 (KHTML, like Gecko) Version/4.0 Mobile Safari/530.17",
    "Mozilla/5.0 (Linux; U; Android 2.2; en-us; Nexus One Build/FRF91) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1",
    "Mozilla/5.0 (Linux; U; Android 1.5; en-us; htc_bahamas Build/CRB17) AppleWebKit/528.5  (KHTML, like Gecko) Version/3.1.2 Mobile Safari/525.20.1",
    "Mozilla/5.0 (Linux; U; Android 2.1-update1; de-de; HTC Desire 1.19.161.5 Build/ERE27) AppleWebKit/530.17 (KHTML, like Gecko) Version/4.0 Mobile Safari/530.17",
    "Mozilla/5.0 (Linux; U; Android 2.2; en-us; Sprint APA9292KT Build/FRF91) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1",
    "Mozilla/5.0 (Linux; U; Android 1.5; de-ch; HTC Hero Build/CUPCAKE) AppleWebKit/528.5  (KHTML, like Gecko) Version/3.1.2 Mobile Safari/525.20.1",
    "Mozilla/5.0 (Linux; U; Android 2.2; en-us; ADR6300 Build/FRF91) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1",
    "Mozilla/5.0 (Linux; U; Android 2.1; en-us; HTC Legend Build/cupcake) AppleWebKit/530.17 (KHTML, like Gecko) Version/4.0 Mobile Safari/530.17",
    "Mozilla/5.0 (Linux; U; Android 1.5; de-de; HTC Magic Build/PLAT-RC33) AppleWebKit/528.5  (KHTML, like Gecko) Version/3.1.2 Mobile Safari/525.20.1 FirePHP/0.3",
    "Mozilla/5.0 (Linux; U; Android 1.6; en-us; HTC_TATTOO_A3288 Build/DRC79) AppleWebKit/528.5  (KHTML, like Gecko) Version/3.1.2 Mobile Safari/525.20.1",
    "Mozilla/5.0 (Linux; U; Android 1.0; en-us; dream) AppleWebKit/525.10  (KHTML, like Gecko) Version/3.0.4 Mobile Safari/523.12.2",
    "Mozilla/5.0 (Linux; U; Android 1.5; en-us; T-Mobile G1 Build/CRB43) AppleWebKit/528.5  (KHTML, like Gecko) Version/3.1.2 Mobile Safari 525.20.1",
    "Mozilla/5.0 (Linux; U; Android 1.5; en-gb; T-Mobile_G2_Touch Build/CUPCAKE) AppleWebKit/528.5  (KHTML, like Gecko) Version/3.1.2 Mobile Safari/525.20.1",
    "Mozilla/5.0 (Linux; U; Android 2.0; en-us; Droid Build/ESD20) AppleWebKit/530.17 (KHTML, like Gecko) Version/4.0 Mobile Safari/530.17",
    "Mozilla/5.0 (Linux; U; Android 2.2; en-us; Droid Build/FRG22D) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1",
    "Mozilla/5.0 (Linux; U; Android 2.0; en-us; Milestone Build/ SHOLS_U2_01.03.1) AppleWebKit/530.17 (KHTML, like Gecko) Version/4.0 Mobile Safari/530.17",
    "Mozilla/5.0 (Linux; U; Android 2.0.1; de-de; Milestone Build/SHOLS_U2_01.14.0) AppleWebKit/530.17 (KHTML, like Gecko) Version/4.0 Mobile Safari/530.17",
    "Mozilla/5.0 (Linux; U; Android 3.0; en-us; Xoom Build/HRI39) AppleWebKit/525.10  (KHTML, like Gecko) Version/3.0.4 Mobile Safari/523.12.2",
    "Mozilla/5.0 (Linux; U; Android 0.5; en-us) AppleWebKit/522  (KHTML, like Gecko) Safari/419.3",
    "Mozilla/5.0 (Linux; U; Android 1.1; en-gb; dream) AppleWebKit/525.10  (KHTML, like Gecko) Version/3.0.4 Mobile Safari/523.12.2",
    "Mozilla/5.0 (Linux; U; Android 2.0; en-us; Droid Build/ESD20) AppleWebKit/530.17 (KHTML, like Gecko) Version/4.0 Mobile Safari/530.17",
    "Mozilla/5.0 (Linux; U; Android 2.1; en-us; Nexus One Build/ERD62) AppleWebKit/530.17 (KHTML, like Gecko) Version/4.0 Mobile Safari/530.17",
    "Mozilla/5.0 (Linux; U; Android 2.2; en-us; Sprint APA9292KT Build/FRF91) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1",
    "Mozilla/5.0 (Linux; U; Android 2.2; en-us; ADR6300 Build/FRF91) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1",
    "Mozilla/5.0 (Linux; U; Android 2.2; en-ca; GT-P1000M Build/FROYO) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1",
    "Mozilla/5.0 (Linux; U; Android 3.0.1; fr-fr; A500 Build/HRI66) AppleWebKit/534.13 (KHTML, like Gecko) Version/4.0 Safari/534.13",
    "Mozilla/5.0 (Linux; U; Android 3.0; en-us; Xoom Build/HRI39) AppleWebKit/525.10  (KHTML, like Gecko) Version/3.0.4 Mobile Safari/523.12.2",
    "Mozilla/5.0 (Linux; U; Android 1.6; es-es; SonyEricssonX10i Build/R1FA016) AppleWebKit/528.5  (KHTML, like Gecko) Version/3.1.2 Mobile Safari/525.20.1",
    "Mozilla/5.0 (Linux; U; Android 1.6; en-us; SonyEricssonX10i Build/R1AA056) AppleWebKit/528.5  (KHTML, like Gecko) Version/3.1.2 Mobile Safari/525.20.1",
]